/**
 * 
 */
package com.store;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jedi.BaseObj;
import com.jedi.User;
import com.tss.util.DbConn;
import com.tss.util.DbRs;

/**
 * @author wevjoso
 *
 */
public class ContractMgr extends BaseObj{
	public Contract get(String id) {
		clearErr();
		
		DbConn conn = new DbConn();
		try {
			String sql = "";
			sql = "select  t.*,s.sgname,s.sgid,case when t.contype =1 then '内部合同' when t.contype=2 then '外部合同' end as contype_name," +
			"u1.user_name as sale_name,u1.user_id,u2.user_id,u2.user_name as techmanager_name," +
			"u3.user_id,u3.user_name as engineer_name,c.comname as com_name ," +
			"b.busname as bus_name ,u4.user_name as createman_name " +
			" from techcontract t left join userinfo u1 on u1.user_id=t.saleid " +
			"left join companyinfo c on c.comid=t.comid   " +
			"left join userinfo u2 on u2.user_id=t.techmanager  " +
			"left join businessinfo b on b.busid=t.busid  " +
			"left join userinfo u4 on u4.user_id=t.createman  " +
			"left join sevgrade s on s.sgid=t.servicelevel  " +
			"left join userinfo u3 on u3.user_id=t.engineer where conid=? ";
			conn.prepare(sql);
			conn.setString(1,id);
			DbRs rs = conn.executeQuery();
			if (rs == null || rs.size() ==0) return null;
			
			Contract contract = new Contract(get(rs,0,"conid"));
			contract.setArea(get(rs,0,"area"));
			contract.setBegindate(getDate(rs,0,"begindate"));
			contract.setConid(getInt(rs,0,"conid"));
			contract.setContractdesc(get(rs,0,"contractdesc"));
			contract.setContractlile(get(rs,0,"contractlile"));
			contract.setCreatetime(getDate(rs,0,"createtime"));
			contract.setCustomer(get(rs,0,"customer"));
			contract.setFifee( getDouble(rs,0,"fifee"));
			contract.setMfee(getDouble(rs,0,"mfee"));
			contract.setEnddate(getDate(rs,0,"enddate"));
			contract.setMyear(getInt(rs,0,"myear"));
			contract.setSpartfee(getDouble(rs,0,"spartfee"));
			contract.setOutefee(getDouble(rs,0,"outefee"));
			//contract.setId(getDouble(rs,0,"id"));

			contract.setLinkman(get(rs,0,"linkman"));

			contract.setPhone(get(rs,0,"phone"));
			contract.setSaleid(get(rs,0,"saleid"));

			contract.setServiceid(get(rs,0,"serviceid"));
			contract.setServicelevel(getInt(rs,0,"servicelevel"));
			contract.setSevbegindate(getDate(rs,0,"sevbegindate"));
			contract.setSevenddate(getDate(rs,0,"sevenddate"));
			contract.setStatus(getInt(rs,0,"status"));

			contract.setTechbegindate(getDate(rs,0,"techbegindate"));
			contract.setTechenddate(getDate(rs,0,"techenddate"));
			contract.setAnalyreport(get(rs,0,"analyreport"));
			contract.setTechsupport(get(rs,0,"techsupport"));
			contract.setComid(getInt(rs,0,"comid"));
			contract.setTechmanager(get(rs,0,"techmanager"));
			contract.setPrjname(get(rs,0,"prjname"));
			contract.setAssigntime(getDate(rs,0,"assigntime"));
			contract.setContype(getInt(rs,0,"contype"));
			contract.setSale_name(get(rs,0,"sale_name"));
			contract.setTechmanager_name(get(rs,0,"techmanager_name"));
			contract.setCom_name(get(rs,0,"com_name"));
			contract.setEngineer(get(rs,0,"engineer"));
			contract.setContype_name(get(rs,0,"contype_name"));
			contract.setBusid(getInt(rs,0,"busid"));
			contract.setBus_name(get(rs,0,"bus_name"));
			contract.setEngineer_name(get(rs,0,"engineer_name"));
			contract.setCreateman_name(get(rs,0,"createman_name"));
			contract.setSignfee(getDouble(rs,0,"signfee"));
			contract.setCreateman(get(rs,0,"createman"));
			Sevgrade sevgrade = new Sevgrade ();
			sevgrade.setSgid(getInt(rs,0,"sgid"));
			sevgrade.setSgname(get(rs,0,"sgname"));
			contract.setSevgrade(sevgrade);
			
			List listEngs = new ArrayList();
			sql = "select t.*,u.*,case when t.taskid ='0' then '未创建'   when ts.status ='0' then '等待' when ts.status='1' then '进行中' when ts.status='2' then '提交' when ts.status='3' then '结束' end as taskstatus_name,ts.status from techconeng t "
					
					+ "Left join userinfo u on t.engid=u.user_id " 
					+ "Left join task ts on t.taskid=ts.taskid " +
					" where t.conid = ? order by t.conid desc";
			conn.prepare(sql);
			conn.setString(1, id);
			rs = conn.executeQuery();
			if (rs != null && rs.size() > 0) {
				for (int i = 0; i < rs.size(); i++) {
					Techconeng item = new Techconeng();
					User u= new User();
					item.setConid(getInt(rs, i, "conid"));
					item.setEngid(get(rs, i, "engid"));
					item.setTaskid(getInt(rs, i, "taskid"));
					item.setEng_name(get(rs,i,"user_name"));
					item.setTaskStatus(getInt(rs,i,"status"));
					item.setTaskStatus_name(get(rs,i,"taskstatus_name"));
					u.setUserName(get(rs,i,"user_name"));
					u.setEmail(get(rs,i,"email"));
					u.setPhone(get(rs,i,"phone"));
					item.setUser(u);
					listEngs.add(item);
				}
			}

			contract.setListEngs(listEngs);
			return contract;
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return null;
		}
		finally {
			conn.close();
		}
	}

	public List get(String otherSQL,String orderField,int pageNo, int pageSize) {
		clearErr();
		DbRs rs = null;
		DbConn conn = null;
		try {
			conn = new DbConn();
			String sqlCount = "select count(conid) as total from techcontract t " +
					"left join userinfo u1 on u1.user_id=t.saleid " +
					"left join userinfo u2 on u2.user_id=t.techmanager " +
					"left join businessinfo b on b.busid=t.busid  " +
					"left join userinfo u4 on u4.user_id=t.createman  " +
					"left join userinfo u3 on u3.user_id=t.engineer where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sqlCount += otherSQL;
			conn.prepare(sqlCount);
			DbRs rsCount = conn.executeQuery();
			int total = getInt(rsCount,0,"total");
			setTotal(total);
			boolean b = total % pageSize == 0;
			int pageCount = b ? (total / pageSize) : (total / pageSize + 1);
			if (pageNo > pageCount) pageNo = pageCount;
			String sql = "select  t.*,case when t.techmanager ='' then '未分配项目经理' when t.techmanager !='' then '已分配' end as status_name,case when t.contype =1 then '内部合同' when t.contype=2 then '外部合同' end as contype_name," +
					"u1.user_name as sale_name,u1.user_id,u2.user_id,u2.user_name as techmanager_name," +
					"u3.user_id,u3.user_name as engineer_name,c.comname as com_name ,u4.user_name as createman_name ," +
					"b.busname as bus_name " +
					" from techcontract t left join userinfo u1 on u1.user_id=t.saleid " +
					"left join companyinfo c on c.comid=t.comid   " +
					"left join userinfo u2 on u2.user_id=t.techmanager  " +
					"left join businessinfo b on b.busid=t.busid  " +
					"left join userinfo u4 on u4.user_id=t.createman  " +
					"left join userinfo u3 on u3.user_id=t.engineer where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			conn.prepare(sql);
			rs = conn.executeQuery(pageNo, pageSize);
		} catch (SQLException ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return new ArrayList();
		} catch (Exception e) {
			e.printStackTrace();
			setErr(e.getMessage());
			return new ArrayList();
		} finally {
			conn.close();
		}
		List r = new ArrayList();
		if (rs == null || rs.size() <= 0)
			return r;
		for (int i = 0; i < rs.size(); i++) {
			Contract contract = new Contract(get(rs,i,"conid"));
			contract.setArea(get(rs,i,"area"));
			contract.setBegindate(getDate(rs,i,"begindate"));
			contract.setConid(getInt(rs,i,"conid"));
			contract.setContractdesc(get(rs,i,"contractdesc"));
			contract.setContractlile(get(rs,i,"contractlile"));
			contract.setCreatetime(getDate(rs,i,"createtime"));
			contract.setCustomer(get(rs,i,"customer"));
			contract.setFifee( getDouble(rs,i,"fifee"));
			contract.setMfee(getDouble(rs,i,"mfee"));
			contract.setEnddate(getDate(rs,i,"enddate"));
			contract.setMyear(getInt(rs,i,"myear"));
			contract.setSpartfee(getDouble(rs,i,"spartfee"));
			contract.setOutefee(getDouble(rs,i,"outefee"));
			contract.setSignfee(getDouble(rs,i,"signfee"));
			//contract.setId(getDouble(rs,i,"id"));

			contract.setLinkman(get(rs,i,"linkman"));

			contract.setPhone(get(rs,i,"phone"));
			contract.setSaleid(get(rs,i,"saleid"));

			contract.setServiceid(get(rs,i,"serviceid"));
			contract.setServicelevel(getInt(rs,i,"servicelevel"));
			contract.setSevbegindate(getDate(rs,i,"sevbegindate"));
			contract.setSevenddate(getDate(rs,i,"sevenddate"));
			contract.setStatus(getInt(rs,i,"status"));

			contract.setTechbegindate(getDate(rs,i,"techbegindate"));
			contract.setTechenddate(getDate(rs,i,"techenddate"));
			contract.setAnalyreport(get(rs,i,"analyreport"));
			contract.setSevbegindate(getDate(rs,i,"techsupport"));
			contract.setComid(getInt(rs,i,"comid"));
			contract.setTechmanager(get(rs,i,"techmanager"));
			contract.setPrjname(get(rs,i,"prjname"));
			contract.setAssigntime(getDate(rs,i,"assigntime"));
			contract.setContype(getInt(rs,i,"contype"));
			contract.setSale_name(get(rs,i,"sale_name"));
			contract.setTechmanager_name(get(rs,i,"techmanager_name"));
			contract.setCom_name(get(rs,i,"com_name"));
			contract.setEngineer(get(rs,i,"engineer_name"));
			contract.setContype_name(get(rs,i,"contype_name"));
			contract.setBusid(getInt(rs,i,"busid"));
			contract.setBus_name(get(rs,i,"bus_name"));
			contract.setStatus_name(get(rs,i,"status_name"));
			contract.setCreateman_name(get(rs,i,"createman_name"));
			contract.setCreateman(get(rs,i,"createman"));
			r.add(contract);
		}
		return r;
	}
	public List conState(String otherSQL, String orderField, int pageNo, int pageSize) {
		clearErr();
		DbRs rs = null;
		DbConn conn = null;
		try {
			conn = new DbConn();
			String sqlCount = "select  count(t.*) as total from techcontract  t where 1=1  ";
			if (otherSQL != null && !otherSQL.trim().equals(""))
			{
				sqlCount += otherSQL;
			}
			sqlCount += " group by t.createman  ";
			conn.prepare(sqlCount);
			DbRs rsCount = conn.executeQuery();
			int  total=rsCount.size();
			//int total = getInt(rsCount, size, "total");
			setTotal(total);
			boolean b = total % pageSize == 0;
			int pageCount = b ? (total / pageSize) : (total / pageSize + 1);
			if (pageNo > pageCount)
				pageNo = pageCount;
			String sql = "select max(u.user_name) as createman_name,count(t.*) as preNum ,sum(t.spartfee+t.outefee+t.signfee) as totfee " +
					" from techcontract t "+
				    " Left Join userinfo u On u.user_id = t.createman "+
				 
					" where 1=1  ";
			if (otherSQL != null && !otherSQL.trim().equals(""))
			{
				sql += otherSQL;
			}
			sql = sql + "  group by t.createman  order by preNum desc";
			conn.prepare(sql);
			rs = conn.executeQuery(pageNo, pageSize);
		} catch (SQLException ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return new ArrayList();
		} catch (Exception e) {
			e.printStackTrace();
			setErr(e.getMessage());
			return new ArrayList();
		} finally {
			conn.close();
		}
		List r = new ArrayList();
		if (rs == null || rs.size() <= 0)
			return r;
		for (int i = 0; i < rs.size(); i++) {
			Contract contract = new Contract();
			contract.setCreateman_name(get(rs, i, "createman_name"));
			contract.setPreNum(getInt(rs, i, "preNum"));//当前登记合同数 
			//contract.setDelcount(getInt(rs, i, "delcount"));//取消项目数
			contract.setTotFee(getDouble(rs,i,"totfee"));
			contract.setOrder(i+1);//名次
			r.add(contract);
		
		}
		return r;
	}
}
